# Replication code for Table 3

# Table 3
# Summary statistics for the consumer credit risk premiums

# This table presents summary statistics for the consumer credit risk premiums for the indicated tranches and
# time periods. The statistics are computed by taking averages across all securitizations in the sample for
# each month, and then averaging the monthly averages over the indicated periods. The consumer credit risk
# premium and the total credit spread are expressed in basis points. Premium/spread percentage denotes the
# ratio of the credit risk premium to the total credit spread of the tranche and is expressed as a percentage.
# Premium/charge-off ratio denotes the ratio of the credit risk premium to the percentage charge-off rate. The
# sample is monthly from January 2000 to January 2020.


##
#' Activate package environment
using Pkg
Pkg.activate(".")
Pkg.status()
##

##
#' Add packages
"CSV" ∉ keys(Pkg.project().dependencies) && Pkg.add("CSV")
"Arrow" ∉ keys(Pkg.project().dependencies) && Pkg.add("Arrow")
"DataFrames" ∉ keys(Pkg.project().dependencies) && Pkg.add("DataFrames")
"Chain" ∉ keys(Pkg.project().dependencies) && Pkg.add("Chain")
"Pipe" ∉ keys(Pkg.project().dependencies) && Pkg.add("Pipe")
"Dates" ∉ keys(Pkg.project().dependencies) && Pkg.add("Dates")
"PrettyTables" ∉ keys(Pkg.project().dependencies) && Pkg.add("PrettyTables")
"FloatingTableView" ∉ keys(Pkg.project().dependencies) && Pkg.add("FloatingTableView")
"TerminalPager" ∉ keys(Pkg.project().dependencies) && Pkg.add("TerminalPager")
"Statistics" ∉ keys(Pkg.project().dependencies) && Pkg.add("Statistics")
"ShiftedArrays" ∉ keys(Pkg.project().dependencies) && Pkg.add("ShiftedArrays")
"Plots" ∉ keys(Pkg.project().dependencies) && Pkg.add("Plots")
"Intervals" ∉ keys(Pkg.project().dependencies) && Pkg.add("Intervals")
##

##
#' load packages
using CSV, Arrow, DataFrames, Chain, Pipe, Dates, PrettyTables, FloatingTableView, TerminalPager, Statistics, ShiftedArrays, Intervals, Random
##


#load data
fileIn = "Data_Table_03.arrow"
CredcardPanel = DataFrame(Arrow.Table(fileIn))



CredcardPremiaPanel = select(CredcardPanel, :Year, :Month, :MPR, :PortfYld, :ChargeOff_1m, :ExSpread, 
    :Riskprem_A, :Riskprem_B, :Riskprem_C, :Crdspread_A, :Crdspread_B, :Crdspread_C,
    :PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C, :PremChrgoffRatio_A, :PremChrgoffRatio_B, :PremChrgoffRatio_C, :Subperiod)


CredcardPanel_m = @chain begin CredcardPremiaPanel
    groupby([:Year,:Month])
    combine([:Riskprem_A, :Riskprem_B, :Riskprem_C, :Crdspread_A, :Crdspread_B, :Crdspread_C, :PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C, 
        :PremChrgoffRatio_A, :PremChrgoffRatio_B, :PremChrgoffRatio_C] .=> (x-> mean(skipmissing(x))), :Subperiod => first, renamecols = false)
    transform!([:PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C] .=> (x->x.*100.0),renamecols=false)
    groupby(:Subperiod)
    combine([:Riskprem_A, :Riskprem_B, :Riskprem_C, :Crdspread_A, :Crdspread_B, :Crdspread_C, :PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C, 
        :PremChrgoffRatio_A, :PremChrgoffRatio_B, :PremChrgoffRatio_C] .=> (x-> mean(skipmissing(x))), renamecols = false)
end
CredcardPanel_All = @chain begin CredcardPremiaPanel
    groupby([:Year,:Month])
    combine([:Riskprem_A, :Riskprem_B, :Riskprem_C, :Crdspread_A, :Crdspread_B, :Crdspread_C, :PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C, 
        :PremChrgoffRatio_A, :PremChrgoffRatio_B, :PremChrgoffRatio_C] .=> (x-> mean(skipmissing(x))), :Subperiod => first, renamecols = false)
    transform!([:PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C] .=> (x->x.*100.0),renamecols=false)
    combine([:Riskprem_A, :Riskprem_B, :Riskprem_C, :Crdspread_A, :Crdspread_B, :Crdspread_C, :PremSpreadRatio_A, :PremSpreadRatio_B, :PremSpreadRatio_C, 
        :PremChrgoffRatio_A, :PremChrgoffRatio_B, :PremChrgoffRatio_C] .=> (x-> mean(skipmissing(x))), renamecols = false)
end
CredcardPanel_All.Subperiod = ["All"]
select!(CredcardPanel_All, :Subperiod, names(CredcardPanel_All,Not(:Subperiod)))
CredcardPanel_m = [CredcardPanel_All; CredcardPanel_m]

CredcardPremiaSumstats = hcat(DataFrame(Period = names(CredcardPanel_m[:, 2:end])), DataFrame(Matrix(CredcardPanel_m[:, 2:end])', string.(CredcardPanel_m.Subperiod)))



# OUTPUT:
#
#  Row │ Period              All       2000-2006  2007-2009  2010-2020 
#      │ String              Float64   Float64    Float64    Float64   
# ─────┼───────────────────────────────────────────────────────────────
#    1 │ Riskprem_A           55.7671   13.6507    194.742     41.2587
#    2 │ Riskprem_B          130.991    20.4262    497.707     92.2347
#    3 │ Riskprem_C          213.025    35.8862    738.48     173.057
# ─────┼───────────────────────────────────────────────────────────────
#    4 │ Crdspread_A          78.6035   60.5112    222.835     42.2412
#    5 │ Crdspread_B         165.802    84.9005    555.213     94.2866
#    6 │ Crdspread_C         266.441   137.939     820.305    175.897
# ─────┼───────────────────────────────────────────────────────────────
#    7 │ PremSpreadRatio_A    71.5602   34.5182     82.1859    98.58
#    8 │ PremSpreadRatio_B    70.3647   31.4445     82.1006    98.5509
#    9 │ PremSpreadRatio_C    68.5945   27.267      80.5507    98.7043
# ─────┼───────────────────────────────────────────────────────────────
#   10 │ PremChrgoffRatio_A   13.4516    3.73433    30.5044    15.4551
#   11 │ PremChrgoffRatio_B   29.4494    5.36579    72.4812    34.1412
#   12 │ PremChrgoffRatio_C   50.8142    8.85944   106.511     65.855



